#!/bin/bash

# 定义变量方便修改
table_db=cy_database
hive=/home/software/hive-2.3.6/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

# 分析预测-行业活跃度榜单

sql="
use cy_database;

with
tmp_business_alive1 as --没有数据支持验证 （这样做了多个当月与上月的数据，但是后续的环比中只取数据最近的日期）
(
select
	industry_code,
	business_code,
	high_tech_talent_num,
	require_talent_num,
	entry_sum,
	quit_num,
	lag(require_talent_num,1) over(partition by industry_code,business_code order by operate_time) as require_talent_num1,
	lag(entry_sum,1) over(partition by industry_code,business_code order by operate_time) as entry_sum1,
	lag(quit_num,1) over(partition by industry_code,business_code order by operate_time) as quit_num1,
	operate_time
from dws_business_alive
),


tmp_business_alive2 as
(
select
	industry_code,
	business_code,
	high_tech_talent_num,
	require_talent_num,
	entry_sum as sign_ldht_sum,
	quit_num,
	require_talent_num1,
	entry_sum1,
	quit_num1,
	concat(cast(((require_talent_num-require_talent_num1)/require_talent_num1*100) as decimal(5,2)) ,'%') as require_percent,
	concat(cast(((entry_sum-entry_sum1)/entry_sum1*100) as decimal(5,2)) ,'%') as entry_percent,
	concat(cast(((quit_num-quit_num1)/quit_num1*100) as decimal(5,2)) ,'%') as wastage_percent,
	operate_time

from tmp_business_alive1
)

insert into table cy_database.ads_big_screen_result_business_alive
select
	industry_code,
	business_code,
	sign_ldht_sum,
	high_tech_talent_num,
	require_talent_num,
	require_percent,
	entry_percent,
	wastage_percent,
	operate_time
from tmp_business_alive2

;
"

$hive -e "$sql"